package Test;

import java.sql.*;
import java.util.Date;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Scanner;

import com.sun.xml.internal.ws.api.streaming.XMLStreamReaderFactory.Default;

public class Test4 {

    private String username = null;
    private String password = null; 
    private Connection connect = null;
    private Statement stmt = null; 
	private static Scanner sc;
	private static String input;
	private static String result[];
	private	static String names_add="";
	private	static String Address_add="";
	private static String profession_add="";
    private static String rs = null;
    private	static int table_choosed_add =-1;
    private	static int Card_type =-1; 

    
	
    /**
     * 设置连接MySQL数据库的账号密码
     * @param username 登陆数据库的账号
     * @param password 登陆数据库的密码
     * 
     */
    public Test4 (String username, String password) {
        this.username = username;
        this.password = password;
    }

    /**
     * 连接MySQL数据库
     * 
     */
    private void openConnection() throws Exception {

    	Class.forName("com.mysql.jdbc.Driver");
        connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/iyoga?useUnicode=true&characterEncoding=UTF-8", username,password);
        System.out.println("Success connect Mysql server");        
        
    }

    /**
     * 往iyoga数据库里人员信息表插入信息
     * @param name 插入人员姓名
     * @param address 插入地址
     * @param profession 插入职位
     * 
     */
    public boolean insertIntoMemberinfoTable(String name, String address, String profession) throws Exception {
        openConnection();
        stmt = connect.createStatement();
        stmt.executeUpdate("insert into memberinfo (Member_name, Address, Profession) values('" + name + "', '" + address + "', '" + profession + "');");
        connect.close();
        return true;
    }

    /**
     * 往iyoga数据库里关系表插入信息
     * @param member_num 插入人员编号
     * @param Card_state 插入卡状态(0代表卡已经过期，1代表卡仍然有效)
     * @param Card_type  插入卡类型("年卡"与"次卡")
     * @param Card_price 插入卡价格
     * @param Usage_date 插入办卡日期(自动获取系统当前日期，次卡缺省)
     * @param Usage_counter 插入使用次数(年卡缺省)
     * 
     */
    public boolean insertIntoRelationshipinfo(String member_num, String card_state, String card_type,String card_price,String usage_date,String usage_counter) throws Exception {
        openConnection();
        stmt = connect.createStatement();
        stmt.executeUpdate("insert into relationshipInfo (Member_num, Card_state, Card_type, Card_price,Usage_date,Usage_counter) values('" + member_num + "', '" + card_state + "', '"+ card_type+"','" +card_price+ "','"+usage_date+"','"+usage_counter+"');");
        connect.close();
        return true;
    }

    /**
     * 在iyoga数据库里查询人员信息表的人员编号
     * 
     */
    public  boolean SearchMember_Num() throws Exception {
        int flag =0;
        openConnection();
        stmt = connect.createStatement();
        String sql="select *from memberinfo where Member_Name ='"+names_add+"'";
        ResultSet resultSet = stmt.executeQuery(sql);
        while(resultSet.next()){
        	rs = resultSet.getString(1);        	
        	flag++;
        	if(flag == 2)
        		 System.out.println("注意：有同名的人！");//同名检查
        }
        connect.close();
        return true;
    }
    
    /**
     * 从控制台接受输入参数
     * @param database  数据库名称
     * @param table_choosed_add 表的选择插入
     * @param Address_add       输入地址
     * @param profession_add    输入职业 
     *  
     */
    public static void input(){
        System.out.println(">");
        sc=new Scanner(System.in);
        input = sc.nextLine();
       }
       
   	public static void get(String input){
   			
   		int void_num = 0;
   		
   		result = new String[input.length()];		

   		for(int i = 0;i<result.length;i++){
   			result[i]=String.valueOf(input.charAt(i));
   		}
   		
   		for(int i = 0;i<result.length;i++){
   			if(result[i].equals("m")){
   				table_choosed_add=0;  //table_choosed_add=0插入人员信息表
   			}
   			if(result[i].equals("c")){
   				table_choosed_add=1;  //table_choosed_add=1插入卡信息表
   			}    			
   		}
   		
   		for(int i = 0;i<result.length;i++){
   		     if(result[i].equals(" ")) 
   				void_num ++;
   				if(void_num == 2)					
   					 names_add+= result[i];
   				if(void_num == 3 ){
   					if(table_choosed_add == 0){
    					 Address_add+= result[i]; 
   					}
   					if(table_choosed_add == 1){
   						
   					} 					
   				}					
   				if(void_num == 4 )
   					if(table_choosed_add == 0){
   					profession_add+= result[i];
   					}
   			}

   		for(int i = 0;i<result.length;i++){   		
   			
   			if(result[i].equals("年")){				
   				Card_type=0; //Card_type=0代表卡的类型是年卡
   				break;
   			}
   			
   			if(result[i].equals("次")){
   				Card_type=1;//Card_type=1代表卡的类型是次卡
   				break;
   			}
   		}		
   	}

    
    public static void main(String[] args) throws Exception {
    	    	
    	SimpleDateFormat bartDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    	Date date= new Date();
        Test4 test = new Test4("root","123456");
    	input();//输入
    	get(input); //获取输入   
        test.insertIntoMemberinfoTable(names_add, Address_add, profession_add);//插入
        test.SearchMember_Num();//查询
        
    	DecimalFormat nFormat= new DecimalFormat("000");
    	int num=Integer.parseInt(rs);//字符转换为整形
    			
        if(table_choosed_add == 0){
    	System.out.println("OK."+names_add+" 's member id is " + nFormat.format(num));
        }
        if(table_choosed_add == 1){  
        	if(Card_type == 0)
        	{
        		test.insertIntoRelationshipinfo(nFormat.format(num), "1","年卡", "10000",bartDateFormat.format(date),"-1"); 
         	    System.out.println("OK.A new 年卡  added to"+names_add+"("+ nFormat.format(num) +")");	
        	}   
        	if(Card_type == 1)
        	{
        		test.insertIntoRelationshipinfo(nFormat.format(num), "1","次卡", "1000",bartDateFormat.format(date),"1000");
        	    System.out.println("OK.A new 次卡  with 100 times added to"+names_add+"("+ nFormat.format(num) +")" );	
                
        	}
        }

    }
}

